/**
 *It is a demo for practicing JAVA. All codes are opened source by Jian.
 */
package jian.tool.daocode.util;

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Jian
 * @date 2012-7-3
 * @version
 * 
 */
public class TableHelper {

	public static List<List<String>> showResultSetMetaData(String dbName,
			String tableName) {

		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<List<String>> result = new ArrayList<List<String>>();
		@SuppressWarnings("unused")
		ParameterMetaData paramMetaData = null;
		ResultSetMetaData rsMetaData = null;
		try {
			/* @TODO 注意此处由于添加注释功能打开了两个连接, 有一个未关闭, 后续请检查并实现关闭(再封装一个方法处理注释) */

			// 获得数据表各个字段的注释
			conn = JdbcUtil.getConn("information_schema");
			String sql = "SELECT * FROM COLUMNS WHERE TABLE_SCHEMA='" + dbName
					+ "' AND TABLE_NAME='" + tableName + "'";
			pstm = conn.prepareStatement(sql);
			paramMetaData = pstm.getParameterMetaData();
			rs = pstm.executeQuery();

			// 将字段名称和字段注释的对应关系保存在HashMap中
			Map<String, String> comments = new HashMap<String, String>();
			while (rs.next()) {
				// System.out.printf("字段名称 %1$s\t\t",
				// rs.getString("column_name"));
				// System.out.printf("字段注释  ");
				// System.out
				// .printf(rs.getString("column_comment").isEmpty() ? "无"
				// : rs.getString("column_comment"));
				// System.out.println();
				comments.put(rs.getString("column_name"),
						rs.getString("column_comment"));
			}

			JdbcUtil.close(rs, pstm, conn);

			conn = JdbcUtil.getConn(dbName);
			sql = "select * from " + tableName;
			pstm = conn.prepareStatement(sql);
			paramMetaData = pstm.getParameterMetaData();
			rs = pstm.executeQuery();
			rsMetaData = rs.getMetaData();
			pstm.getParameterMetaData();

			int cols = rsMetaData.getColumnCount(); // 查询获取结果的列数

			for (int i = 1; i <= cols; i++) {
				List<String> columnData = new ArrayList<String>();
				columnData.add(rsMetaData.getColumnName(i)); // 列名

				// 对于byte[]类型的特殊处理,当为二进制数组是为 [B
				if (rsMetaData.getColumnClassName(i).length() <= 5) {
					columnData.add("java.lang.byte[]");
				} else {
					columnData.add(rsMetaData.getColumnClassName(i)); // Java
																		// Type
				}
				columnData.add(rsMetaData.getColumnTypeName(i)); // SQL Type
				columnData.add(rsMetaData.getColumnDisplaySize(i) + ""); // Max
																			// Display
																			// Size
				columnData.add(comments.get(rsMetaData.getColumnName(i)));
				result.add(columnData);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, pstm, conn);
		}
		return result;
	}
}
